/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.services.loan;

/**
 *
 * @author jay
 */

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.model.SelectItem;
import oracle.jdbc.OracleTypes;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import src.HibernateUtil;
import src.domain.loan.LoanInstallment;
import src.domain.loan.Loanpmt;
import src.services.CRUEDService;
import src.util.Constanta;

public class LoanpmtModel implements java.io.Serializable {
    
    private CRUEDService service;
    
    public LoanpmtModel() {
        service = new CRUEDService();
    }
    
    public Loanpmt getByNumber(String value) {
        Map<String, String> where = new HashMap<>();
        where.put("pmt.paymentNo", value);
        return (Loanpmt) service.getObjectSQL("from Loanpmt pmt", where);
    }
    
    public List<Loanpmt> findAll(Map<String, String> filters) {
        return (List<Loanpmt>) service.findAll("Loanpmt", filters);
    }
    
    public List<SelectItem> listAll() {
        return service.listAll("Loanpmt", "loanpmtId","paymentNo");
    }
    
    //public BigDecimal getLoanpmtAmount(Integer id) {
   //     return (BigDecimal) service.getObjectSQLdirect("SELECT getLoanpmtAmount("+id+") FROM DUAL");
   // }    

    public boolean saveData(Loanpmt data, LoanInstallment install) {
        
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success = false;
        
        try {
            
            session.saveOrUpdate(data);
            install.setPaymentId(data.getLoanpmtId());
            session.update(install);
            session.flush();
            
            Query q = session.createSQLQuery(" { call loan_modul.loanpmt_actions (?) }");
            q.setInteger(0,data.getLoanpmtId());  
            q.executeUpdate();
                
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {
                    Logger.getLogger(LoanpmtModel.class.getName()).log(Level.SEVERE, null, e1);
                }
            }
            success = false;  
            Logger.getLogger(LoanpmtModel.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        return success;  
                        
            /*/update loan balance
            Query q = session.createSQLQuery(" { call SAVE_LOAN_BALANCE(?,?,?) }");
            q.setInteger(0,data.getLoan().getLoanId());  // first parameter, index starts with 0
            q.setBigDecimal(1, data.getAmount()); 
            q.setString(2, "C"); 
            q.executeUpdate();
            */
    }
    
    public boolean deleteData(Loanpmt data) {
        return service.deleteData(data);
    }
    
    public  Loanpmt calculateAmount(Integer id) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        ResultSet result;
        Loanpmt hasil = new Loanpmt();
        
        CallableStatement callableStatement;
              
        try {
            callableStatement = con.prepareCall("{call loan_modul.calc_loanpmt_amount(?,?)}");
            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callableStatement.setInt(2, id);

            callableStatement.executeQuery();
     
            result = (ResultSet) callableStatement.getObject(1);
            
            if(result.next()) {
                //principalAmount, interestAmount, amount    
                hasil.setPrincipalAmount(result.getBigDecimal(1));
                hasil.setInterestAmount(result.getBigDecimal(2));
                hasil.setAmount(result.getBigDecimal(3));
            }

            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(LoanpmtModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hasil;
    }
    
}
